#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Build-in / Std

'''
Created on 2016年9月21日

@author: Hawk
'''

from __future__ import absolute_import

try:
    import cStringIO as StringIO
except ImportError:
    import StringIO

# Standard Library
import re
import string

# Third Party Stuff
import xlsxwriter

excel_file_chose_wildcard = "Excel worksheet(*.xlsx)|*.xlsx"
excel_file_chose_caption = ""

EXCEL_RANGE_PATTERN = re.compile(r'([a-zA-Z]+)([\d]+):([a-zA-Z]+)([\d]+)')

XLSXWRITER_FORMAT_PROPERTIES = (
    'font_name',
    'font_size',
    'font_color',
    'bold',
    'italic',
    'underline',
    'font_strikeout',
    'font_script',
    'num_format',
    'locked',
    'hidden',
    'text_h_align',
    'text_v_align',
    'rotation',
    'text_wrap',
    'text_justlast',
    #'center_across',
    'indent',
    'shrink',
    'pattern',
    'bg_color',
    'fg_color',
    'bottom',
    'top',
    'left',
    'right',
    'bottom_color',
    'top_color',
    'left_color',
    'right_color',
)

def duplicate_xlsxwriter_format_object(workbook, old_format):
    properties = {}
    if old_format is not None:
        for property_name in XLSXWRITER_FORMAT_PROPERTIES:
            properties[property_name] = getattr(old_format, property_name)

    return workbook.add_format(properties)

def col2num(col):
    num = 0
    for c in col:
        if c in string.ascii_letters:
            num = num * 26 + (ord(c.upper()) - ord('A')) + 1
    return num

def excel_range_string_to_indices(range_string):
    try:
        first_col_name, first_row, last_col_name, last_row = EXCEL_RANGE_PATTERN.findall(
            range_string)[0]
    except IndexError:
        raise ValueError("Invalid range string.")

    first_col_index = col2num(first_col_name) - 1
    first_row_index = int(first_row) - 1
    last_col_index = col2num(last_col_name) - 1
    last_row_index = int(last_row) - 1

    return (
        first_col_index,
        first_row_index,
        last_col_index,
        last_row_index
    )

def apply_border_to_cell(workbook, worksheet, row_index, col_index, format_properties):
    try:
        cell = worksheet.table[row_index][col_index]
        new_format = duplicate_xlsxwriter_format_object(workbook, cell.format)

        # Convert properties in the constructor to method calls.
        for key, value in format_properties.items():
            getattr(new_format, 'set_' + key)(value)

        # Update cell object
        worksheet.table[row_index][col_index] = cell = cell._replace(format=new_format)
    except KeyError:
        format = workbook.add_format(format_properties)
        worksheet.write(row_index, col_index, None, format)

def apply_outer_border_to_range(workbook, worksheet, options=None):
    options = options or {}

    border_style = options.get("border_style", 1)
    range_string = options.get("range_string", None)

    if range_string is not None:
        first_col_index, first_row_index, last_col_index, last_row_index = excel_range_string_to_indices(
            range_string)
    else:
        first_col_index = options.get("first_col_index", None)
        last_col_index = options.get("last_col_index", None)
        first_row_index = options.get("first_row_index", None)
        last_row_index = options.get("last_row_index", None)

        all_are_none = all(map(lambda x: x is None, [
            first_col_index,
            last_col_index,
            first_row_index,
            last_row_index,
        ]))

        if all_are_none:
            raise Exception("You need to specify the range")

    for row_index in range(first_row_index, last_row_index + 1):
        left_border = {
            "left": border_style,
        }
        right_border = {
            "right": border_style,
        }

        apply_border_to_cell(workbook, worksheet, row_index, first_col_index, left_border)
        apply_border_to_cell(workbook, worksheet, row_index, last_col_index, right_border)

    for col_index in range(first_col_index, last_col_index + 1):

        top_border = {
            "top": border_style,
        }

        bottom_border = {
            "bottom": border_style,
        }

        apply_border_to_cell(workbook, worksheet, first_row_index, col_index, top_border)
        apply_border_to_cell(workbook, worksheet, last_row_index, col_index, bottom_border)

    top_left_border = {
        "top": border_style,
        "left": border_style,
    }
    apply_border_to_cell(workbook, worksheet, first_row_index, first_col_index, top_left_border)

    top_right_border = {
        "top": border_style,
        "right": border_style,
    }
    apply_border_to_cell(workbook, worksheet, first_row_index, last_col_index, top_right_border)

    bottom_left_border = {
        "bottom": border_style,
        "left": border_style,
    }
    apply_border_to_cell(workbook, worksheet, last_row_index, first_col_index, bottom_left_border)

    bottom_right_border = {
        "bottom": border_style,
        "right": border_style,
    }
    apply_border_to_cell(workbook, worksheet, last_row_index, last_col_index, bottom_right_border)

#
defalut_format = {
                'font_name': 'Times New Roman',
                'align': 'center',
                'font_size': 12,
                'valign': 'vcenter'}
default_align_left_format = {
                    'font_name': 'Times New Roman',
                    'align': 'left',
                    'font_size': 12,
                    'valign': 'vcenter'}
green_bkg_format = {
                    'font_name': 'Times New Roman',
                    'font_size': 12,
                    'align': 'center',
                    'valign': 'vcenter',
                    'fg_color': '#00b050'}
yellow_bkg_format = {
                    'font_name': 'Times New Roman',
                    'align': 'center',
                    'font_size': 12,
                    'valign': 'vcenter',
                    'fg_color': 'yellow'}
red_bkg_format = {
                    'font_name': 'Times New Roman',
                    'align': 'center',
                    'font_size': 12,
                    'valign': 'vcenter',
                    'fg_color': 'red'}
header_format = {
                'font_name': 'Times New Roman',
                'align': 'center',
                'bold': True,
                'font_size': 13,
                'valign': 'vcenter',
                }
date_format = {
               'num_format': 'yyyy-mm-dd',
               'font_name': 'Times New Roman',
               'align': 'left',
               'font_size': 12,
               'valign': 'vcenter'
               }

def write_cell(workbook, worksheet, row_idx, col_idx, content, format=defalut_format, row_height=1, col_width=1):
    cell_format = workbook.add_format(format)
    if row_height == 1 and col_width == 1:
        worksheet.write(row_idx, col_idx, content, cell_format)
    else:
        target_row = row_idx + row_height - 1
        target_col = col_idx + col_width - 1
        worksheet.merge_range(row_idx, col_idx, target_row, target_col, content, cell_format)

def group_data(item_list):
    items = list(set(item_list))
    group_idx_list = sorted([item_list.index(item) for item in items])
    group_idx_list.append(group_idx_list[-1] + 1 if group_idx_list[-1] == len(item_list) - 1 else len(item_list) - 1)
    return [(group_idx, item_list[group_idx] if group_idx < len(item_list) else '') for group_idx in group_idx_list]

def group_col_data(col_idx, grid, fixed_row_count):
    group_idx_list = group_data([grid.GetCellValue(row_idx, col_idx).strip() for row_idx in range(fixed_row_count, grid.GetNumberRows())])
    group_idx_list = [(idx[0] + fixed_row_count, idx[1]) for idx in group_idx_list]

    return group_idx_list

def apply_outer_border(workbook, worksheet, col_idx, start_idx_list, last_col_idx=None):
    last_col_idx = last_col_idx or col_idx
    for i in range(len(start_idx_list) - 1):
        apply_outer_border_to_range(
            workbook,
            worksheet,
            {
            "first_row_index": start_idx_list[i][0],
            "first_col_index": col_idx,
            "last_row_index": start_idx_list[i + 1][0] - 1,
            "last_col_index": last_col_idx,
            "border_style": 2,
            },
        )

def apply_group(workbook, worksheet, col_idx, group_idx_list, fixed_row_count):
    for i in range(len(group_idx_list) - 1):
        write_cell(workbook, worksheet, group_idx_list[i][0], col_idx, group_idx_list[i][1], row_height=group_idx_list[i + 1][0] - group_idx_list[i][0], format=default_align_left_format)

def group_and_outer_border(workbook, worksheet, col_idx, grid, fixed_row_count):
    group_idx_list = group_col_data(col_idx, grid, fixed_row_count)

    apply_group(workbook, worksheet, col_idx, group_idx_list, fixed_row_count)

    apply_outer_border(workbook, worksheet, col_idx, group_idx_list)

    return group_idx_list

def gen_export_xlsx_file_name(prefix=None):
    _prefix = ""
    if prefix is not None and len(prefix) > 0:
        _prefix = str(prefix) + "_"

    import datetime
    return _prefix + datetime.datetime.now().strftime("%Y-%m-%d-%H-%M-%S") + ".xlsx"

if __name__ == "__main__":
    # create a workbook in memory
    output = StringIO.StringIO()

    workbook = xlsxwriter.Workbook(output, {"in_memory": True})

    worksheet = workbook.add_worksheet()
    worksheet.set_landscape()
    worksheet.set_paper(8)
    worksheet.set_margins(0.787402, 0.787402, 0.5, 0.787402)
    worksheet.write('A1', 'Hello world')
    worksheet.merge_range('B4:B5', 'Merged Range')
    apply_outer_border_to_range(
        workbook,
        worksheet,
        {
            "range_string": "B3:G10",
            "border_style": 5,
        },
    )

    apply_outer_border_to_range(
        workbook,
        worksheet,
        {
            "first_col_index": 0,
            "first_row_index": 0,
            "last_col_index": 4,
            "last_row_index": 4,
            "border_style": 5,
        },
    )

    # Close the workbook before streaming the data.
    workbook.close()

    with open("./sample.xlsx", "wb") as f:
        f.write(output.getvalue())
